package com.tianqu.tms.www.settle.action;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import com.tianqu.tms.core.base.TmsAction;
import com.tianqu.tms.core.base.UserContext;
import com.tianqu.tms.core.util.DateUtil;
import com.tianqu.tms.core.util.StringUtil;
import com.tianqu.tms.core.util.TmsConstants;
import com.tianqu.tms.dao.TmsDao;
import com.tianqu.tms.dao.TransferSettlementInfoDao;
import com.tianqu.tms.dto.TransferSettlementInfoDto;
import com.tianqu.tms.entity.Codes;
import com.tianqu.tms.entity.TransferSettlementInfo;
import com.tianqu.tms.entity.User;
import com.tianqu.tms.www.settle.viewhelper.TransferSettleViewHelper;
/**
 * 导出excel表格
 * @author Administrator
 *
 */
public class TransferSettleExportExcelAction extends TmsAction {

	@Override
	protected ActionForward doAction(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response,
			UserContext uc) throws Exception {
		
		String insertUserId = request.getParameter("userid");
		
		//根据userID查询导出
		
		TransferSettleViewHelper view = (TransferSettleViewHelper)uc.getAttribute(TmsConstants.VIEWHELP);
		
		if(!"0".equals(insertUserId))
		{
			User user = new User();
			user = user.getUserInfoById(Integer.parseInt(insertUserId));
			view.setChargeName(user.getRealName());
		}		
//		//流程表收费
//		TransferSettlementInfo getYPrice1 = new TransferSettlementInfo();
//		getYPrice1 = getYPrice1.getYPriceByUserId(insertUserId,1,view.getSettleId());
//		view.setYprice1(String.valueOf(getYPrice1.getYsPrice()));
//		view.setSprice1(String.valueOf(getYPrice1.getSsPrice()));
//		//交易票收费
//		TransferSettlementInfo getYPrice2 = new TransferSettlementInfo();
//		getYPrice2 = getYPrice2.getYPriceByUserId(insertUserId,3,view.getSettleId());
//		view.setYprice2(String.valueOf(getYPrice2.getYsPrice()));
//		view.setSprice2(String.valueOf(getYPrice2.getSsPrice()));
//		//待办收费
//		TransferSettlementInfo getYPrice3 = new TransferSettlementInfo();
//		getYPrice3 = getYPrice3.getYPriceByUserId(insertUserId,6,view.getSettleId());
//		view.setYprice3(String.valueOf(getYPrice3.getYsPrice()));
//		view.setSprice3(String.valueOf(getYPrice3.getSsPrice()));
//		//待办超期/过期收费
//		TransferSettlementInfo getYPrice4 = new TransferSettlementInfo();
//		getYPrice4 = getYPrice4.getYPriceByUserId(insertUserId,7,view.getSettleId());
//		view.setYprice4(String.valueOf(getYPrice4.getYsPrice()));
//		view.setSprice4(String.valueOf(getYPrice4.getSsPrice()));
//		//变更交易票收费
//		TransferSettlementInfo getYPrice5 = new TransferSettlementInfo();
//		getYPrice5 = getYPrice5.getYPriceByUserId(insertUserId,5,view.getSettleId());
//		view.setYprice5(String.valueOf(getYPrice5.getYsPrice()));
//		view.setSprice5(String.valueOf(getYPrice5.getSsPrice()));
//		//流程表退费
//		TransferSettlementInfo getYPrice6 = new TransferSettlementInfo();
//		getYPrice6 = getYPrice6.getYPriceByUserId(insertUserId,2,view.getSettleId());
//		view.setYprice6(String.valueOf(getYPrice6.getYsPrice()));
//		view.setSprice6(String.valueOf(getYPrice6.getSsPrice()));
//		int ylctf = getYPrice6.getYsPrice();
//		int slctf = getYPrice6.getSsPrice();
//		//交易票退费
//		TransferSettlementInfo getYPrice7 = new TransferSettlementInfo();
//		getYPrice7 = getYPrice7.getYPriceByUserId(insertUserId,4,view.getSettleId());
//		view.setYprice7(String.valueOf(getYPrice7.getYsPrice()));
//		view.setSprice7(String.valueOf(getYPrice7.getSsPrice()));
//		ylctf = ylctf + getYPrice7.getYsPrice();
//		slctf = slctf + getYPrice7.getSsPrice();
//		//总计：
//		TransferSettlementInfo getYPrice8 = new TransferSettlementInfo();
//		getYPrice8 = getYPrice8.getTotlePriceByUserId(insertUserId,view.getSettleId());
//		view.setYprice8(String.valueOf(getYPrice8.getYsPrice()-ylctf*2));
//		view.setSprice8(String.valueOf(getYPrice8.getSsPrice()-slctf*2));
		
		view.setUserId(insertUserId);
		
		uc.setAttribute("settleInsertUserId", insertUserId);
		
		//TODO 20180727
		Codes code = Codes.getInstance();
		TransferSettlementInfoDao uDao = (TransferSettlementInfoDao) TmsDao.getInstance(TmsDao.TMS_TRANSFER_SETTLEMENT_INFO);
		List<TransferSettlementInfoDto> list = uDao.getPriceByUserId(Integer.parseInt(insertUserId), view.getSettleId());
		int sumReceivable = 0;
		int sumPaid = 0;
		int sumReceivableBack = 0;
		int sumPaidBack = 0;
		for (int n = 0; n < list.size(); n++) {
			TransferSettlementInfoDto dto = list.get(n);
			dto.setChargeName(view.getChargeName());
			dto.setStartDate(view.getStartDate());
			dto.setEndDate(view.getEndDate());
			dto.setNum(n+1);
			dto.setSettleTypeName(code.getItemValueByItemIdAndItemCode(Codes.CHARGE_TYPE_CHARGE, dto.getSettletype()));
			if(dto.getNum()%2==0) dto.setCssFlag(1); else dto.setCssFlag(2);
			sumReceivable = sumReceivable + dto.getReceivable();
			sumPaid = sumPaid + dto.getPaid();
			sumReceivableBack = sumReceivableBack + dto.getReceivableBack();
			sumPaidBack = sumPaidBack + dto.getPaidBack();
		}
		List<Codes> types = code.getCodesListByItemId(Codes.CHARGE_TYPE_CHARGE);
		Collections.sort(types, new Comparator<Codes>() {  
            @Override  
            public int compare(Codes o1, Codes o2) {  
                return o1.getItemCode()>o2.getItemCode() ? 1 : -1;  
            }  
        });  
		
		List<TransferSettlementInfoDto> viewList = new ArrayList<TransferSettlementInfoDto>();
		for (int n = 0; n < types.size(); n++) {
			Codes tmpCode = types.get(n);
			boolean f = true;
			for (TransferSettlementInfoDto dto : list) {
				if(dto.getSettletype() == tmpCode.getItemCode()) {
					dto.setNum(n+1);
					if(dto.getNum()%2==0) dto.setCssFlag(1); else dto.setCssFlag(2);
					viewList.add(dto);
					f = false;
					break;
				} 
			}
			if(f) {
				TransferSettlementInfoDto dto = new TransferSettlementInfoDto();
				dto.setNum(n+1);
				dto.setChargeName(view.getChargeName());
				dto.setStartDate(view.getStartDate());
				dto.setEndDate(view.getEndDate());
				dto.setSettleTypeName(tmpCode.getItemValue());
				if(dto.getNum()%2==0) dto.setCssFlag(1); else dto.setCssFlag(2);
				dto.setReceivable(0);
				dto.setReceivableBack(0);
				dto.setPaid(0);
				dto.setPaidBack(0);
				dto.setReceivableStr("0");
				dto.setReceivableBackStr("0");
				dto.setPaidStr("0");
				dto.setPaidBackStr("0");
				viewList.add(dto);
			}
		}
		
		view.setInfo(viewList);
		view.setSumReceivable(StringUtil.addDH(String.valueOf(sumReceivable)));
		view.setSumReceivableBack(StringUtil.addDH(String.valueOf(sumReceivableBack)));
		view.setSumPaid(StringUtil.addDH(String.valueOf(sumPaid)));
		view.setSumPaidBack(StringUtil.addDH(String.valueOf(sumPaidBack)));
		
		
        // 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet("财务结算");  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        HSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        HSSFCellStyle style = wb.createCellStyle();  
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        style.setBorderBottom((short) 1);   
        style.setBorderTop((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1); 
        
        sheet.setDefaultRowHeightInPoints(10);
        sheet.setDefaultColumnWidth(17);
        
        HSSFFont  font  =  wb.createFont(); 
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        font.setFontHeightInPoints((short)8);
        style.setFont(font);
          
        HSSFCellStyle cellStyle = wb.createCellStyle();  
		HSSFFont titleFont = wb.createFont();
		titleFont.setFontName("宋体");
		titleFont.setFontHeight((short) 200);
		titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        
        cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式  
        cellStyle.setBorderBottom((short) 1);   
        cellStyle.setBorderTop((short) 1);
        cellStyle.setBorderLeft((short) 1);
        cellStyle.setBorderRight((short) 1); 
        cellStyle.setFont(titleFont);
        
        String[] title = {"序号","收费员","结算开始日期","结算截至日期","结算业务","应收金额(元)","应退金额(元)","实收金额(元)","实退金额(元)"};
        for (int n = 0; n < title.length; n++) {
            HSSFCell cell = row.createCell((short) n);  
            cell.setCellValue(title[n]);  
            cell.setCellStyle(cellStyle); 
        }
        
        for (int n = 0; n < list.size(); n++) {
        	TransferSettlementInfoDto dto = list.get(n);
        	row = sheet.createRow(n+1);
        	HSSFCell c0 = row.createCell((short) 0);
        	c0.setCellStyle(style);
        	c0.setCellValue(n+1);  
        	HSSFCell c1 = row.createCell((short) 1);
        	c1.setCellStyle(style);
        	c1.setCellValue(dto.getChargeName()); 
	        HSSFCell c2 = row.createCell((short) 2);
	        c2.setCellStyle(style);
	        c2.setCellValue(dto.getStartDate());
	        HSSFCell c3 = row.createCell((short) 3);
	        c3.setCellStyle(style);
	        c3.setCellValue(dto.getEndDate());
	        HSSFCell c4 = row.createCell((short) 4);
	        c4.setCellStyle(style);
	        c4.setCellValue(dto.getSettleTypeName());
        	HSSFCell c5 = row.createCell((short) 5);
        	c5.setCellStyle(style);
        	c5.setCellValue(StringUtil.addDH(String.valueOf(dto.getReceivable()))); 
        	HSSFCell c6 = row.createCell((short) 6);
        	c6.setCellStyle(style);
        	c6.setCellValue(StringUtil.addDH(String.valueOf(dto.getReceivableBack()))); 
        	HSSFCell c7 = row.createCell((short) 7);
        	c7.setCellStyle(style);
        	c7.setCellValue(StringUtil.addDH(String.valueOf(dto.getPaid()))); 
        	HSSFCell c8 = row.createCell((short) 8);
        	c8.setCellStyle(style);
        	c8.setCellValue(StringUtil.addDH(String.valueOf(dto.getPaidBack()))); 
        }
        
        
        
//        HSSFCell cell = row.createCell((short) 0);  
//        cell.setCellValue("序号");  
//        cell.setCellStyle(cellStyle);  
//
//        HSSFCell cell1 = row.createCell((short) 1);  
//        cell1.setCellValue("收费员");  
//        cell1.setCellStyle(cellStyle); 
//        
//        HSSFCell cell2 = row.createCell((short) 2);  
//        cell2.setCellValue("结算开始日期");  
//        cell2.setCellStyle(cellStyle); 
//        
//        HSSFCell cell3 = row.createCell((short) 3);  
//        cell3.setCellValue("结算截至日期");  
//        cell3.setCellStyle(cellStyle); 
//        
//        HSSFCell cell4 = row.createCell((short) 4);  
//        cell4.setCellValue("结算业务");  
//        cell4.setCellStyle(cellStyle); 
//        
//        HSSFCell cell5 = row.createCell((short) 5);  
//        cell5.setCellValue("应收/退金额(元)");  
//        cell5.setCellStyle(cellStyle); 
//        
//        HSSFCell cell6 = row.createCell((short) 6);  
//        cell6.setCellValue("实收/退金额(元)");  
//        cell6.setCellStyle(cellStyle); 
//        
//        for(int i=1;i<8;i++)
//        {
//        	row = sheet.createRow(i);
//        	HSSFCell c0 = row.createCell((short) 0);
//        	c0.setCellStyle(style);
//        	c0.setCellValue(i);  
//        	HSSFCell c1 = row.createCell((short) 1);
//        	c1.setCellStyle(style);
//        	c1.setCellValue(view.getChargeName()); 
//	        HSSFCell c2 = row.createCell((short) 2);
//	        c2.setCellStyle(style);
//	        c2.setCellValue(view.getStartDate());
//	        HSSFCell c3 = row.createCell((short) 3);
//	        c3.setCellStyle(style);
//	        c3.setCellValue(view.getEndDate());
//	        HSSFCell c4 = row.createCell((short) 4);
//	        c4.setCellStyle(style);
//        	HSSFCell c5 = row.createCell((short) 5);
//        	c5.setCellStyle(style);
//        	HSSFCell c6 = row.createCell((short) 6);
//        	c6.setCellStyle(style);
//	        if(i==1)
//	        {
//	        	c4.setCellValue("流程表收费");
//	        	c5.setCellValue(StringUtil.addDH(view.getYprice1()));  
//	        	c6.setCellValue(StringUtil.addDH(view.getSprice1()));  
//	        }
//	        if(i==2)
//	        {
//	        	c4.setCellValue("交易票收费");
//	        	c5.setCellValue(StringUtil.addDH(view.getYprice2()));  
//	        	c6.setCellValue(StringUtil.addDH(view.getSprice2()));  
//	        }
//	        if(i==3)
//	        {
//	        	c4.setCellValue("待办收费");
//	        	c5.setCellValue(StringUtil.addDH(view.getYprice3()));  
//	        	c6.setCellValue(StringUtil.addDH(view.getSprice3()));  
//	        }
//	        if(i==4)
//	        {
//	        	c4.setCellValue("待办超期/过期收费");
//	        	c5.setCellValue(StringUtil.addDH(view.getYprice4()));  
//	        	c6.setCellValue(StringUtil.addDH(view.getSprice4()));  
//	        }
//	        if(i==5)
//	        {
//	        	c4.setCellValue("变更交易票收费");
//	        	c5.setCellValue(StringUtil.addDH(view.getYprice5()));  
//	        	c6.setCellValue(StringUtil.addDH(view.getSprice5()));  
//	        }
//	        if(i==6)
//	        {
//	        	c4.setCellValue("流程表退费");
//	        	if(!"0".equals(view.getYprice6())&&!"".equals(view.getYprice6()))
//	        	{
//	        		c5.setCellValue("-"+StringUtil.addDH(view.getYprice6()));  
//	        	}else{
//	        		c5.setCellValue("0");
//	        	}
//	        	if(!"0".equals(view.getSprice6())&&!"".equals(view.getSprice6()))
//	        	{
//	        		c6.setCellValue("-"+StringUtil.addDH(view.getSprice6()));  
//	        	}else{
//	        		c6.setCellValue("0");  
//	        	}
//	        }
//	        if(i==7)
//	        {
//	        	c4.setCellValue("交易票退费");
//	        	if(!"0".equals(view.getYprice7())&&!"".equals(view.getYprice7()))
//	        	{
//	        		c5.setCellValue("-"+StringUtil.addDH(view.getYprice7()));  
//	        	}else{
//	        		c5.setCellValue("0");
//	        	}
//	        	if(!"0".equals(view.getSprice7())&&!"".equals(view.getSprice7()))
//	        	{
//	        		c6.setCellValue("-"+StringUtil.addDH(view.getSprice7()));  
//	        	}else{
//	        		c6.setCellValue("0");  
//	        	}
//	        }
//        }
        try  
        {  
        	File file =new File(toUtf8String(view.getChargeName()+""+DateUtil.getCurrentTimeStr()+".xls"));
        	OutputStream  fout = new FileOutputStream(file);  
            wb.write(fout); 
            getResponse(file, response);
        }  
        catch (Exception e)  
        {  
            e.printStackTrace();  
        } 
//        uc.setAttribute("tsxx", "1");
        request.setAttribute(TmsConstants.VIEWHELP, view);
		return null;
	}
}
